package beans;

import java.sql.*;
import java.util.*;
import javax.ejb.*;

/**
 *
 * @author KAV
 */
public class EmployeesBean extends AbstractBean {

    private static final String EMPLOYEES="employees";
    private static final String SELECTENTITYEMPLOYEE="SELECT name, surname, job, NVL(manager, 0), hiredate, salary, department FROM ";
    private static final String INSERTEMPLOYEES="INSERT INTO employees(name, surname, job, manager, hiredate, salary, department) VALUES(?, ?, ?, ?, TO_DATE(?, 'YYYY-MM-DD'), ?, ?)";
    private static final String UPDATEEMPLOYEES="UPDATE employees SET name=?, surname=?, job=?, manager=?, hiredate=TO_DATE(?, 'YYYY-MM-DD'), salary=?, department=?";
    public int id;
    public String name;
    public String surname;
    public String job;
    public int manager;
    public String hiredate;
    public int salary;
    public int department;

    protected PreparedStatement queryForRemoveObj(Connection conn) throws SQLException {
        return conn.prepareStatement(DELETE+EMPLOYEES+WHEREID);
    }

    protected PreparedStatement findBy(Connection conn, String byId) throws SQLException {
        return conn.prepareStatement(SELECTBYID+EMPLOYEES+byId);
    }

    protected void loadEntity(PreparedStatement st, Connection conn) throws SQLException, NoSuchEntityException {
        st = conn.prepareStatement(SELECTENTITYEMPLOYEE+EMPLOYEES+WHEREID);
        st.setInt(1, Integer.parseInt(context.getPrimaryKey().toString()));
        ResultSet rs = st.executeQuery();
        if (!rs.next()) {
            throw new NoSuchEntityException("Can't load next employee");
        }
        name = rs.getString(1);
        surname = rs.getString(2);
        job = rs.getString(3);
        manager = rs.getInt(4);
        hiredate = rs.getString(5).substring(0, 11);
        salary = rs.getInt(6);
        department = rs.getInt(7);
    }

    protected PreparedStatement storeEntity(Connection conn) throws SQLException {
        PreparedStatement st = conn.prepareStatement(UPDATEEMPLOYEES+WHEREID);
        st.setString(1, name);
        st.setString(2, surname);
        st.setString(3, job);
        if (manager == 0) {
            st.setNull(4, java.sql.Types.INTEGER);
        } else {
            st.setInt(4, manager);
        }
        st.setString(5, hiredate);
        st.setInt(6, salary);
        st.setInt(7, department);
        st.setInt(8, Integer.parseInt(context.getPrimaryKey().toString()));
        return st;
    }

    public Integer ejbCreate(String name, String surname, String job, int manager, String hiredate, int salary, int department) throws CreateException {
        this.name = name;
        this.surname = surname;
        this.job = job;
        this.manager = manager;
        this.hiredate = hiredate;
        this.salary = salary;
        this.department = department;
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = ds.getConnection();
            st = conn.prepareStatement(INSERTEMPLOYEES);
            st.setString(1, name);
            st.setString(2, surname);
            st.setString(3, job);
            if (manager == 0) {
                st.setNull(4, java.sql.Types.INTEGER);
            } else {
                st.setInt(4, manager);
            }
            st.setString(5, hiredate);
            st.setInt(6, salary);
            st.setInt(7, department);
            if (st.executeUpdate() != 1) {
                throw new CreateException("Can't create employee, bad parametrs");
            }
            st = conn.prepareStatement(SELECTMAXID+EMPLOYEES);
            ResultSet rs = st.executeQuery();
            rs.next();
            /*if (!rs.next()) {
                throw new CreateException("...");
            }*/
            int result = rs.getInt(1);
            return new Integer(result);
        } catch (SQLException ex) {
            throw new EJBException(ex);
        } finally {
            Util.closeConnection(conn, st);
        }
    }

    public void ejbPostCreate(String name, String surname, String job, int manager, String hiredate, int salary, int department) throws CreateException {
    }

    public Collection ejbFindByNameOrSurname(String query) {
        Collection c = new LinkedList();
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = ds.getConnection();
            st = conn.prepareStatement("SELECT e.id FROM employees e, departments d, employees e2 WHERE e.department=d.id AND ((e.manager IS NULL AND e.id=e2.id) OR e.manager=e2.id) AND (UPPER(e.name) LIKE UPPER('%" + query + "%') OR UPPER(e.surname) LIKE UPPER('%" + query + "%'))");
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                int keys = rs.getInt(1);
                c.add(new Integer(keys));
            }
        } catch (SQLException ex) {
            throw new EJBException(ex);
        } finally {
            Util.closeConnection(conn, st);
        }
        return c;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSurname() {
        return surname;
    }

    public void setSurname(String surname) {
        this.surname = surname;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public int getManager() {
        return manager;
    }

    public void setManager(int manager) {
        this.manager = manager;
    }

    public String getDate() {
        return hiredate;
    }

    public void setDate(String date) {
        this.hiredate = date;
    }

    public int getSalary() {
        return salary;
    }

    public void setSalary(int salary) {
        this.salary = salary;
    }

    public int getDepartment() {
        return department;
    }

    public void setDepartment(int department) {
        this.department = department;
    }
}
